﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using UniMana.Data;
using System.Text;

namespace UniMana.Data
{
    public static class ReportSubjectListData_Chi
    {
        public static IQueryable CaSubjectList(this Table<Training_Subject> traSub,
                                       Table<Catalogue_Department> cataDep,
                                       Table<Catalogue_SubjectType> cataSubType,
                                       Table<Training_TrainingProgram_Detail>traTraDet,
                                       Table<Training_SubjectGroup> TraSubGro, int _iDep)
        {
            var view1 = from t in cataDep
                        join t0 in traSub on new { Catalogue_DepartmentKey = t.Catalogue_DepartmentKey } equals new { Catalogue_DepartmentKey = Convert.ToInt32(t0.Catalogue_DepartmentKey) }
                        join t1 in traTraDet on t0.Training_SubjectKey equals t1.Training_SubjectKey
                        join t2 in TraSubGro on new { Training_SubjectGroupKey = Convert.ToInt32(t1.Training_SubjectGroupKey) } equals new { Training_SubjectGroupKey = t2.Training_SubjectGroupKey }
                        join t3 in cataSubType on new { Catalogue_SubjectTypeKey = Convert.ToInt32(t1.Catalogue_SubjectTypeKey) } equals new { Catalogue_SubjectTypeKey = t3.Catalogue_SubjectTypeKey }
                        group new { t, t0, t3, t2 } by new
                        {
                            t.Catalogue_DepartmentKey,
                            t.Name,
                            t0.Training_SubjectKey,
                            t0.ID,
                            Column1 = t0.Name,
                            t3.Catalogue_SubjectTypeKey,
                            Column2 = t3.Name,
                            t2.Training_SubjectGroupKey,
                            Column3 = t2.Name
                        } into g
                        where g.Key.Catalogue_DepartmentKey == _iDep
                        select new
                        {
                            Catalogue_DepartmentKey = (System.Int32?)g.Key.Catalogue_DepartmentKey,
                            TenKhoa = g.Key.Name,
                            Training_SubjectKey = (System.Int32?)g.Key.Training_SubjectKey,
                            g.Key.ID,
                            TenMonHoc = g.Key.Column1,
                            Catalogue_SubjectTypeKey = (System.Int32?)g.Key.Catalogue_SubjectTypeKey,
                            TenLoaiMH = g.Key.Column2,
                            Training_SubjectGroupKey = (System.Int32?)g.Key.Training_SubjectGroupKey,
                            TenNhomKT = g.Key.Column3
                        };
            return view1;
        }
    }
}
